package br.zeos;

import br.zeos.util.POG;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ZConnect {

    private String HostName = "127.0.0.1";
    private String UserName = "root";
    private String Password = "";
    private String DataBase = "mysql";
    private String Protocol = "mysql";
    private Boolean Open = false;
    private Connection conexao = null;

    /**
     * Create a new Connection
     *
     * @param HostName
     * @param UserName
     * @param Password
     * @param DataBase
     * @param Protocol
     */
    public ZConnect(String HostName, String UserName, String Password, String DataBase, String Protocol) {
        this.HostName = (HostName == null) ? "" : HostName;
        this.UserName = (UserName == null) ? "" : UserName;
        this.Password = (Password == null) ? "" : Password;
        this.DataBase = (DataBase == null) ? "" : DataBase;
        this.Protocol = (Protocol == null) ? "" : Protocol;
    }

    public ZConnect() {
    }

    /**
     * Open the connection
     */
    public void Open() {
        try {
            if (this.Protocol.equalsIgnoreCase("mysql")) {
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                this.conexao = DriverManager.getConnection("jdbc:mysql://" + this.HostName + "/" + this.DataBase, this.UserName, this.Password);
                this.Open = !conexao.isClosed();
            } else if (this.Protocol.equalsIgnoreCase("postgresql")) {
                Class.forName("org.postgresql.Driver").newInstance();
                this.conexao = DriverManager.getConnection("jdbc:postgresql://" + this.HostName + "/" + this.DataBase, this.UserName, this.Password);
                this.Open = !conexao.isClosed();
            } else if (this.Protocol.equalsIgnoreCase("sqlite")) {
                Class.forName("org.sqlite.JDBC").newInstance();
                this.conexao = DriverManager.getConnection("jdbc:sqlite:" + this.HostName);
                this.Open = !conexao.isClosed();
            }
        } catch (Exception e) {
            //error Log ?
            e.printStackTrace();
            this.Open = false;
        }
    }

    public void Close() {
        try {
            this.conexao.close();
            this.Open = !conexao.isClosed();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String getHostName() {
        return HostName;
    }

    public void setHostName(String HostName) {
        this.HostName = HostName;
    }

    public String getUserName() {
        return UserName;
    }

    public void setUserName(String UserName) {
        this.UserName = UserName;
    }

    public String getPassword() {
        return Password;
    }

    public void setPassword(String Password) {
        this.Password = Password;
    }

    public String getDataBase() {
        return DataBase;
    }

    public void setDataBase(String DataBase) {
        this.DataBase = DataBase;
    }

    public String getProtocol() {
        return Protocol;
    }

    public void setProtocol(String Protocol) {
        this.Protocol = Protocol;
    }

    public Boolean getOpen() {
        return Open;
    }

    public void setOpen(Boolean Open) {
        this.Open = Open;
    }

    public Connection getConexao() {
        return conexao;
    }

    private static ArrayList<HashMap<String, String>> _get(ResultSet r) {
        ArrayList<HashMap<String, String>> retorno = new ArrayList<HashMap<String, String>>();
        try {
            ResultSetMetaData re = r.getMetaData();
            while (r.next()) {
                HashMap<String, String> m = new HashMap<String, String>();
                for (int i = 1; i <= re.getColumnCount(); i++) {
                    //aplicar fix de retorno da image correta aqui 
                    String tipo = re.getColumnTypeName(i);
                    if (tipo.contains("BLOB") || tipo.contains("BYTE")) {
                        m.put(re.getColumnName(i).toUpperCase(), POG.byteToSQL(r.getBytes(i)));
                    } else if (tipo.contains("TIMESTAMP") || tipo.contains("DATETIME") || tipo.contains("HOUR") || tipo.contains("DATE")) {

                        String Value = r.getString(i).trim();
                        if (!Value.isEmpty()) {
                            try {
                                String formato = "";
                                if (Value.split("[0-9]{4}\\-").length > 1) {
                                    formato = "yyyy-MM-dd";
                                } else {
                                    formato = ((SimpleDateFormat) DateFormat.getDateInstance(DateFormat.MEDIUM, java.util.Locale.getDefault())).toPattern();
                                }
                                if (Value.split("\\s[0-9]{2}\\:").length > 1) {
                                    formato += " HH:mm:ss";
                                }
                                SimpleDateFormat formatador = new SimpleDateFormat(formato);
                                try {

                                    java.util.Date d = formatador.parse(Value);
//                                    SimpleDateFormat f2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    SimpleDateFormat f2 = new SimpleDateFormat(((SimpleDateFormat) DateFormat.getDateInstance(DateFormat.MEDIUM, java.util.Locale.getDefault())).toPattern()+" HH:mm:ss");
                                    Value = f2.format(d);
                                } catch (Exception e) {
//                                    e.printStackTrace();
                                }
                            } catch (Exception e) {
//                                e.printStackTrace();
                            }
                        }

                        m.put(re.getColumnName(i).toUpperCase(), Value);
                    } else {
                        m.put(re.getColumnName(i).toUpperCase(), r.getString(i));
                    }
                }
                retorno.add(m);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return retorno;
    }

    public void DirectExec(String sql) {
        try {
            this.getConexao().createStatement().execute(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public ArrayList<HashMap<String, String>> DirectQuery(String sql) {
        ArrayList<HashMap<String, String>> retorno = new ArrayList<HashMap<String, String>>();
        try {
            Statement s = this.getConexao().createStatement();
            retorno = _get(s.executeQuery(sql));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return retorno;
    }
}
